package cn.vastbase.jdbc.bigdata;

import cn.com.vastbase.core.types.PGClob;
import org.junit.Test;

import java.io.*;
import java.nio.file.Files;
import java.sql.*;

public class TextTestCase {

    static Connection conn = null;
    static String cname = "org.postgresql.Driver";
    static String url = "jdbc:postgresql://192.168.56.12:6000/test?loggerLevel=OFF";
    static String username = "test";
    static String passwd = "Vastdata#0730";
    static CallableStatement cs = null;
    static Statement stmt = null;
    static PreparedStatement ps = null;
    static ResultSet rs = null;

    static Reader reader = null;
    static Writer writer = null;

    static InputStream is = null;
    static OutputStream os = null;


    /**
     * CREATE TABLE test_text (id int, msg text);
     */
    @Test
    public void testText() {
        if (conn() == null) {
            return;
        }
        try {
            ps = conn.prepareStatement("delete from test_text");
            ps.execute();

            // 读取文件文本并写入text字段
            reader = new InputStreamReader(Files.newInputStream(new File("src/main/resources/infile.txt").toPath()));
            ps = conn.prepareStatement("INSERT INTO test_text VALUES (?, ?)");
            ps.setInt(1, 1);
            ps.setCharacterStream(2, reader);
            ps.executeUpdate();
            reader.close();
            System.out.println("读取并写入数据库完成");

            // 读取数据库text字段值，保存到文件
            ps = conn.prepareStatement("SELECT msg FROM test_text WHERE id= ?");
            ps.setInt(1, 1);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                reader = rs.getCharacterStream(1);
                writer = new OutputStreamWriter(Files.newOutputStream(new File("src/main/resources/outfile.txt").toPath()));
                char[] c = new char[1024];
                int i;
                while ((i = reader.read(c)) != -1) {
                    writer.write(c, 0, i);
                }
            }
            System.out.println("读取并保存文本到文件完成");
        } catch (Exception e) {
            System.out.println("[FAIL]  fail." + e.getMessage());
        } finally {
            close();
        }
    }


    /**
     * CREATE TABLE test_clob(uid integer, clob_text clob);
     */
    @Test
    public void testClob() {
        if (conn() == null) {
            return;
        }
        try {
            ps = conn.prepareStatement("delete from test_clob");
            ps.execute();

            ps = conn.prepareStatement("INSERT INTO test_clob VALUES (?, ?)");
            ps.setInt(1, 1);
            ps.setString(2, "温故而知新，可以为师矣");
            ps.executeUpdate();
            System.out.println("读取并写入数据库完成");

            // 读取数据库text字段值，保存到文本
            ps = conn.prepareStatement("SELECT clob_text FROM test_clob WHERE uid= ?");
            ps.setInt(1, 1);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                PGClob object = (PGClob) rs.getObject(1);
                Reader reader = object.getCharacterStream();
                char[] chars = new char[1024];
                int read = reader.read(chars);
                System.out.println("1"+new String(chars));
                System.out.println(object.getClass().getName());
                System.out.println(rs.getString(1));
                reader.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close();
        }
    }


    public Connection conn() {
        try {
            Class.forName(cname);
            return conn = DriverManager.getConnection(url, username, passwd);
        } catch (ClassNotFoundException | SQLException e) {
            System.out.println("[FAIL] conn database fail." + e.getMessage());
        }
        return null;
    }

    public void close() {
        try {
            if (rs != null)
                rs.close();
        } catch (SQLException e) {
            System.out.println("[FAIL] close resultSet fail." + e.getMessage());
        }
        try {
            if (cs != null)
                cs.close();
        } catch (SQLException e) {
            System.out.println("[FAIL] close callableStatement fail." + e.getMessage());
        }
        try {
            if (stmt != null)
                stmt.close();
        } catch (SQLException e) {
            System.out.println("[FAIL] close statement fail." + e.getMessage());
        }
        try {
            if (ps != null)
                ps.close();
        } catch (SQLException e) {
            System.out.println("[FAIL] close preparedStatement fail." + e.getMessage());
        }
        try {
            if (conn != null)
                conn.close();
        } catch (SQLException e) {
            System.out.println("[FAIL] close connection fail." + e.getMessage());
        }
        try {
            if (writer != null)
                writer.close();
        } catch (IOException e) {
            System.out.println("[FAIL] close writer fail." + e.getMessage());
        }
        try {
            if (reader != null)
                reader.close();
        } catch (IOException e) {
            System.out.println("[FAIL] close reader fail." + e.getMessage());
        }
        try {
            if (os != null)
                os.close();
        } catch (IOException e) {
            System.out.println("[FAIL] close outputStream fail." + e.getMessage());
        }
        try {
            if (is != null)
                is.close();
        } catch (IOException e) {
            System.out.println("[FAIL] close inputStream fail." + e.getMessage());
        }

    }
}
